package com.cy.module.excel;

import com.alibaba.fastjson.JSONObject;
import com.cy.BasicController;
import com.cy.config.EnumConst;
import com.cy.ext.plugin.cache.NoCache;
import com.cy.ext.plugin.route.ControllerBind;
import com.cy.model.Account;
import com.cy.model.License;
import com.cy.model.LicenseToken;
import com.cy.model.Order;
import com.cy.model.PackageInfo;
import com.cy.model.Point;
import com.cy.model.PointLog;
import com.cy.model.ProductLibrary;
import com.cy.model.RoleLabel;
import com.cy.model.User;
import com.cy.model.UserMapRole;
import com.cy.utils.RandomCodeGenerator;
import com.cy.utils.excel.ExcelBean;
import com.cy.utils.excel.ExcelTools;
import com.jfinal.aop.Before;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.jfinal.upload.UploadFile;
import org.apache.commons.lang3.StringUtils;

import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@NoCache
@ControllerBind(controllerKey = "/res/_webApi/excel")
public class ExcelController extends BasicController {

    private static String[] titleCode = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S"};

    @Before(Tx.class)
    public void index() throws ParseException, IOException {
        JSONObject jsonObject = new JSONObject();
        int code;
        Account account = getLoginAccount();
        //获取当前用户Id
        if (account != null) {
            UploadFile uploadFile = null;
            try {
                //接收excel文件
                uploadFile = getFile();
            }catch (RuntimeException e){}
            List<List<Object>> excel = null;

            //获取参数
            String t = getPara();
            ExcelBean excelBean = new ExcelBean();
            excelBean.setSheetNo(0);
            excelBean.setStart(1);
            excelBean.setEnd(0);
            try{
                if(null != uploadFile){
                    excel = ExcelTools.readSheet(uploadFile.getFile(), excelBean);//excel sheet数据
                }
                if ("user".equals(t)) {//导入用户数据
                    jsonObject = excel2user(excel, account.getAccountId());
                }else if ("account".equals(t)) {//导入account数据
                    jsonObject = excel2account(excel, account.getAccountId());
                }else if("order".equals(t)){  //导入订单
                    jsonObject = excel2order(excel);
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            code = EnumConst.RetCode.SUCCESS.getCode();
        } else {
            code = EnumConst.RetCode.NOT_SIGNIN.getCode();
        }
        jsonObject.put("code", code);
        renderJson(jsonObject);
    }

    private JSONObject listToJson(List list){
        JSONObject result = new JSONObject();
        for (int i = 0 ; i < list.size() ; i++){
            result.put(titleCode[i],list.get(i));
        }
        return result;
    }


    //保存
    private JSONObject excel2order(List<List<Object>> excel) throws ParseException {
        JSONObject result = new JSONObject();
        int count = 0;
        StringBuilder msg = new StringBuilder();

        List<JSONObject> successExcel = new ArrayList<JSONObject>();
        successExcel.add(listToJson(excel.get(0)));
        for (int i = 1; i < excel.size(); i++) {
            List<Object> row = excel.get(i);
            String orderNo = getRowValue(row, 0).trim();
            String oriOrderNo = getRowValue(row, 1).trim();
            String userName = getRowValue(row, 2);
            String produceCode = getRowValue(row, 3).trim();
            String numStr = getRowValue(row, 4).trim();
            String shopName = getRowValue(row, 5);
            String expressNo = getRowValue(row, 6).trim();
            String phone = getRowValue(row, 7);
            String addr = getRowValue(row, 8);
            String moneyStr = getRowValue(row, 9);
            String ratioStr = getRowValue(row, 10);
            int num;
            BigDecimal money;
            BigDecimal ratio;

            if (StringUtils.isNotBlank(orderNo)&&StringUtils.isNotBlank(oriOrderNo)&&StringUtils.isNotBlank(produceCode)
                    &&StringUtils.isNotBlank(numStr)&&StringUtils.isNotBlank(expressNo)&&StringUtils.isNotBlank(phone)
                    &&StringUtils.isNotBlank(moneyStr)&&StringUtils.isNotBlank(ratioStr)){
                Order order = Order.dao.findByOrderNoAndProductNo(orderNo, produceCode);
                if(null != order){
                    msg.append("订单:").append(orderNo).append("和货品编号:").append(produceCode).append("已经存在请不要重复导入;<br>");
                    continue;
                }
                order = new Order();

                try {
                    Double tempRatio = Double.valueOf(ratioStr);
                    //兑换率范围0-5
                    if (!(tempRatio >= 0 && tempRatio <= 5))
                        throw new NumberFormatException();
                    num = Integer.parseInt(numStr);
                    money = new BigDecimal(moneyStr);
                    ratio = new BigDecimal(ratioStr);
                }catch (NumberFormatException e){
                    msg.append("订单:").append(orderNo).append("导入失败，数据格式不正确;<br>");
                    continue;
                }

                if(num < 1){
                    msg.append("订单:").append(orderNo).append("购买数量不正确;<br>");
                    continue;
                }
                //如果有相应的套装信息才能导入
                PackageInfo packageInfo = PackageInfo.dao.findByTag(produceCode);
                if(null != packageInfo){
                    try{
                        //如果手机没注册用户则默认生成
                        String userMobile = "";
                        if (phone.length() >=  11){
                            Pattern p = Pattern.compile("^(13[0-9]|15[0|3|6|7|8|9]|18[8|9])\\d{8}");
                            Matcher m = p.matcher(userMobile);
                            if(m.find()){
                                userMobile = m.group();
                            }else {
                                userMobile = phone.substring(0,11);
                            }
                        }else {
                            userMobile = phone;
                        }

                        User user = User.dao.findByMobile(userMobile);
                        if(null == user){
                            user = new User();
                            String shareCode = RandomCodeGenerator.randomNumberAndLetter(4);
                            user.setAccount(userMobile).setNickName(userName).setCreateTime(new Date()).setMobile(userMobile).setPassword("123456").setStatus(0).setShareCode(shareCode).save();
                            //生成license账号
                            License license = new License();
                            license.setUserId(user.getUserId()).setStatus(0).save();
                        }
                        order.setUserId(user.getUserId());

                        //创建订单
                        order.setOrderNo(orderNo).setOriginalOrderNo(oriOrderNo).setUserName(userName).setProductNo(produceCode)
                                .setCount(num).setShopName(shopName).setExpressNo(expressNo).setPhone(phone).setAddress(addr)
                                .setCreateTime(new Date());
                        if (!order.save()){
                            msg.append("订单:").append(orderNo).append("导入失败;<br>");
                            continue;
                        }

                        //每个套装 生成一个token
                        for(int j = 0 ;j < num ;j++){
                            LicenseToken licenseToken = new LicenseToken();
                            //创建licenseToken
                            licenseToken.setNum(packageInfo.getNum()).setCreateTime(new Date()).setType(LicenseToken.Type.ORDER.getCode())
                                    .setStatus(0).setOrderId(order.getOrderId()).save();
                        }

                        //叮币 = 金额 x 兑换率
                        int p = (int) Math.floor(money.multiply(ratio).doubleValue());
                        //写入日志
                        PointLog log = new PointLog();
                        Calendar calendar = Calendar.getInstance();
                        log.setUserId(user.getUserId()).setPointRuleId(0L).setPointNum(p).setCreateTime(calendar.getTime());
                        calendar.set(calendar.get(calendar.YEAR)+1,11,31);
                        log.setExpireTime(calendar.getTime()).setType(2).setCreater(0).setActionCount(1);
                        if (log.save()){
                            //增加用户积分
                            Point point = Point.dao.findByUserAndType(user.getUserId(), Point.PointType.BEAN.getType());
                            if (point != null){
                                point.addPoint(p);
                                point.update();
                            } else {
                                Point temp = new Point();
                                temp.setUserId(user.getUserId()).setPointAvailable(Long.valueOf(p)).setPointFrozen(Long.valueOf(0))
                                        .setType(Point.PointType.BEAN.getType()).save();
                            }
                        }

                        log("订单:"+order.getOrderId()+",通过批量导入添加成功!");
                        count++;
                        successExcel.add(listToJson(row));
                    }catch (Exception e){
                        e.printStackTrace();
                        getLog().warn("订单:"+orderNo+",通过批量导入异常!");
                    }

                }else{
                    msg.append("订单:").append(orderNo).append("没有对应的套装;<br>");
                    getLog().warn("订单:"+orderNo+",通过批量导入失败，原因：没有对应的套装!");
                }
            }else{
                msg.append("订单:").append(orderNo).append("数据不完整;<br>");
                getLog().warn("订单:"+orderNo+",通过批量导入失败，原因：数据不完整!");
            }
        }
        result.put("count",count);
        result.put("msg",msg.toString());
        result.put("successData",successExcel);
        return result;
    }

    private JSONObject excel2user(List<List<Object>> excel, long accountId) throws ParseException {
        JSONObject result = new JSONObject();
        int count = 0;
        StringBuilder msg = new StringBuilder();
        if (accountId < 1){
            return result;
        }
        List<List<Object>> successExcel = new ArrayList<List<Object>>();
        successExcel.add(excel.get(0));
        for (int i = 1; i < excel.size(); i++) {
            List<Object> row = excel.get(i);
            String accountStr = getRowValue(row, 1).trim();
            String mobile = getRowValue(row, 2).trim();
            String password = getRowValue(row, 3).trim();
            String typeStr = getRowValue(row, 4).trim();
            String statusStr = getRowValue(row, 5).trim();
            String name = getRowValue(row, 6).trim();
            String remark = getRowValue(row, 7);
            String realName = getRowValue(row, 8);
            String email = getRowValue(row, 9);
            String wx = getRowValue(row, 10);
            String sex = getRowValue(row, 11);
            String birthDay = getRowValue(row, 12);
            String levelStr = getRowValue(row, 13);
            if(StringUtils.isEmpty(password)){
                password = "123456";
            }
            if (StringUtils.isEmpty(statusStr)){
                statusStr = "0";
            }
            int status = 0 ;
            int sexInt = 1 ;
            Date bDay;
            User user = new User();
            if (StringUtils.isNotBlank(mobile)&&StringUtils.isNotBlank(statusStr)){
                switch (statusStr){
                    case "激活":
                        status = 1;
                        break;
                    case "冻结":
                        status = 0;
                        break;
                }
                RoleLabel role = null;
                if(StringUtils.isNotEmpty(typeStr) && (role = RoleLabel.dao.findByName(typeStr)) == null){
                    msg.append("客户账号:").append(mobile).append("的类型不明确;");
                    continue;
                }
                if(StringUtils.isNotEmpty(birthDay)){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-ddhh:mm:ss");
                    try{
                        bDay = sdf.parse(birthDay);
                        user.setBirthDay(bDay);
                    }catch (ParseException e){}
                }
                if(StringUtils.isNotEmpty(sex)){
                    switch (typeStr){
                        case "男":
                            sexInt = 1;
                            break;
                        case "女":
                            sexInt = 0;
                            break;
                    }
                   user.setSex(sexInt);
                }

                if(StringUtils.isEmpty(accountStr)){
                    accountStr = mobile;
                }

                if (!User.dao.checkAccountUnique(mobile)){
                    user.setAccount(accountStr).setPassword(password).setMobile(mobile).setStatus(status).setCreateTime(new Date()).setRemark(remark)
                            .setRealName(realName).setNickName(name).setEmail(email).setWx(wx);
                    if(user.save()){
                        UserMapRole userMapRole = new UserMapRole();
                        if (role.getLevel() == 0){//测试用户
                            long level = UserMapRole.Level.MANAGER.getValue();
                            for (UserMapRole.Level item : UserMapRole.Level.values()){
                                if (item.getName().equals(levelStr)){
                                    level = item.getValue();
                                    break;
                                }
                            }
                            userMapRole.setUserRoleLevel(level);
                        }
                        userMapRole.setUserId(user.getUserId());
                        userMapRole.setRoleId(role.getRoleId());
                        userMapRole.setCreateTime(new Date());
                        userMapRole.setStatue(1);
                        userMapRole.save();

                        //生成license账号
                        License license = new License();
                        license.setUserId(user.getUserId()).setStatus(1).save();
                    }
                    count++;
                    log("客户账号:"+user.getUserId()+",通过批量导入添加成功!");
                    successExcel.add(row);
                }else{
                    msg.append("客户账号:").append(mobile).append("已存在;");
                    getLog().warn("客户账号:" + mobile + ",通过批量导入出错，原因：已存在账号!");
                }
            }
        }
        result.put("count",count);
        result.put("msg",msg.toString());
        return result;
    }

    private JSONObject excel2account(List<List<Object>> excel, long accountId) throws ParseException {
        JSONObject result = new JSONObject();
        int count = 0;
        StringBuilder msg = new StringBuilder();
        if (accountId < 1){
            return result;
        }
        List<List<Object>> successExcel = new ArrayList<List<Object>>();
        successExcel.add(excel.get(0));
        for (int i = 1; i < excel.size(); i++) {
            List<Object> row = excel.get(i);
            String accountStr = getRowValue(row, 1).trim();
            String password = getRowValue(row, 2).trim();
            String typeStr = getRowValue(row, 3).trim();
            String statusStr = getRowValue(row, 4).trim();
            String name = getRowValue(row, 5).trim();
            String mobile = getRowValue(row, 6).trim();
            String department = getRowValue(row, 7);
            String jobNo = getRowValue(row, 8);
            String email = getRowValue(row, 9);
            String remark = getRowValue(row, 10);
            if(StringUtils.isEmpty(password)){
                password = "123456";
            }
            if (StringUtils.isEmpty(statusStr)){
                statusStr = "0";
            }
            int status;

            Account account = new Account();
            if (StringUtils.isNotBlank(accountStr)&&StringUtils.isNotBlank(typeStr)){
                switch (statusStr){
                    case "激活":
                        status = 1;
                        break;
                    case "冻结":
                        status = 0;
                        break;
                    case "离职":
                        status = 2;
                        break;
                    default:
                        status = Integer.parseInt(statusStr);
                        break;
                }
                if (!Account.dao.checkAccountUnique(accountStr)){
                    account.setName(name).setAccount(accountStr).setPassword(password).setLevel(1).setMobile(mobile).setStatus(status)
                            .setDepartment(department).setJobNo(jobNo).setEmail(email).setRemark(remark).setCreateTime(new Date()).save();
                    count++;
                    log("账号:"+account.getAccountId()+",通过批量导入添加成功!");
                    successExcel.add(row);
                }else{
                    msg.append("账号:").append(accountStr).append("已存在;");
                    getLog().warn("账号:" + accountStr + ",通过批量导入出错，原因：已存在账号!");
                }
            }
        }
        result.put("count",count);
        result.put("msg",msg.toString());
        return result;
    }

    public ProductLibrary getProduct(List<Object> row){

        String productNum = getRowValue(row, 1).trim();
        String name = getRowValue(row, 2).trim();
        String title = getRowValue(row, 3).trim();
        String company = getRowValue(row, 4).trim();
        String brand = getRowValue(row, 5).trim();
        String taste = getRowValue(row, 6).trim();
        String category = getRowValue(row, 7).trim();
        String desc = getRowValue(row, 8);
        String mainPicture = getRowValue(row, 9);
        String desPicture = getRowValue(row, 10);
        String url = getRowValue(row, 12);
        int status = 1 ;
        Integer categoryInt = null;
        switch (category){
            case "咖啡":
                categoryInt = 1;
                break;
            case "茶":
                categoryInt = 2;
                break;
            case "热巧克力奶":
                categoryInt = 3;
                break;
            case "果汁":
                categoryInt = 4;
                break;

        }

        ProductLibrary product = new ProductLibrary();
        if (StringUtils.isNotBlank(name) && StringUtils.isNotBlank(productNum)){
            product.setProductNum(productNum).setName(name).setDescription(desc).setTitle(title).setDesPicture(desPicture)
            .setCompany(company).setBrand(brand).setTaste(taste).setCategory(categoryInt).setMainPicture(mainPicture).setCreateTime(new Date()).setStatus(status);
            if(StringUtils.isNotEmpty(url)){
                product.setIsMapping(1).setUrl(url);
            }
            if(StringUtils.isNotEmpty(desc)){
                desc=desc.replaceAll("(\r\n|\r|\n|\n\r)", "<br>");
                product.setDescription(desc);
            }
            return  product;
        }
        return null;
    }

    //获取excel列的值
    public static String getRowValue(List<Object> row, int index) {
        String rowValue = "";
        try {
            rowValue = row.get(index).toString();
        } catch (Exception e) {
        }

        return rowValue;
    }

    //性别 类型装换
    private static int getSex(String sexStr) {
        int sex = 0;
        if ("男".equals(sexStr)) {
            sex = 1;
        }
        return sex;
    }

}